﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;

namespace NetworkTest
{
    //Excel辅助类
    public class ExcelHelper
    {
        //创建Excel模板
        public static string CreateExcelTemplate()
        {
            //创建Workbook
            IWorkbook workbook = new XSSFWorkbook();
            //创建Sheet1
            ISheet sheet1 = workbook.CreateSheet("设备IP地址");
            //创建4行
            for (int i = 0; i < 4; i++)
            {
                //创建行
                sheet1.CreateRow(i);
            }

            //获取行
            IRow row1 = sheet1.GetRow(0);
            IRow row2 = sheet1.GetRow(1);
            IRow row3 = sheet1.GetRow(2);
            IRow row4 = sheet1.GetRow(3);

            //创建8列
            ICell[] cell1 = new ICell[8];
            ICell[] cell2 = new ICell[8];
            ICell[] cell3 = new ICell[8];
            ICell[] cell4 = new ICell[8];

            //创建单元格
            for (int i = 0; i < cell1.Length; i++)
            {
                cell1[i] = row1.CreateCell(i);
                cell2[i] = row2.CreateCell(i);
                cell3[i] = row3.CreateCell(i);
                cell4[i] = row4.CreateCell(i);
            }

            //第1行-写入数据
            cell1[0].SetCellValue("");
            cell1[1].SetCellValue("工站1");
            cell1[2].SetCellValue("工站2");
            cell1[3].SetCellValue("工站3");
            cell1[4].SetCellValue("工站4");
            cell1[5].SetCellValue("工站5");
            cell1[6].SetCellValue("工站6");
            cell1[7].SetCellValue("工站7");
            //第2行-写入数据
            cell2[0].SetCellValue("设备1");
            cell2[1].SetCellValue(IPHelper.LocalIP);
            cell2[2].SetCellValue("192.168.1.1");
            cell2[3].SetCellValue("192.168.1.2");
            cell2[4].SetCellValue("192.168.1.3");
            cell2[5].SetCellValue("192.168.1.4");
            cell2[6].SetCellValue("192.168.1.5");
            cell2[7].SetCellValue("192.168.1.6");
            //第3行-写入数据
            cell3[0].SetCellValue("设备2");
            cell3[1].SetCellValue("127.0.0.1");
            cell3[2].SetCellValue("192.168.100.100");
            cell3[3].SetCellValue("192.168.100.101");
            //第4行-写入数据
            cell4[0].SetCellValue("设备3");
            cell4[1].SetCellValue("192.168.10.10");
            cell4[2].SetCellValue("192.168.10.11");
            cell4[3].SetCellValue("192.168.10.12");

            //创建Sheet2
            ISheet sheet2 = workbook.CreateSheet("说明");
            //创建行
            IRow row21 = sheet2.CreateRow(0);
            IRow row22 = sheet2.CreateRow(1);
            //创建单元格
            ICell cell20 = row21.CreateCell(0);
            ICell cell21 = row22.CreateCell(0);
            //写入数据
            cell20.SetCellValue("项目名称");
            cell21.SetCellValue("第一个sheet中的内容，列标题工站1，工站2等可以修改名称；\r\n行标题设备1，设备2也可以修改名称；\r\n工站、设备不够可以继续添加；\r\n上面的项目名称可以修改为实际的项目名称");

            //模板路径
            string path = $"{Application.StartupPath}\\{Program.TEMPLATE_NAME}";
            //写入文件
            FileStream fileStream = new FileStream(path, FileMode.Create);
            workbook.Write(fileStream);
            //关闭流
            fileStream.Close();
            workbook.Close();

            //返回模板路径
            return path;
        }

        //读取项目名称
        public static string ReadProjectName(string path)
        {
            //创建Workbook
            IWorkbook workbook = new XSSFWorkbook(path);
            //获取Sheet2
            ISheet sheet2 = workbook.GetSheetAt(1);
            //获取行
            IRow row21 = sheet2.GetRow(0);
            //获取单元格
            ICell cell20 = row21.GetCell(0);
            //获取值
            string value = cell20.StringCellValue;
            //关闭流
            workbook.Close();

            //返回
            return value;
        }

        //读取Excel获取内容
        public static List<string[]> GetContent(string path)
        {
            //创建Workbook
            IWorkbook workbook = new XSSFWorkbook(path);
            //获取Sheet1
            ISheet sheet1 = workbook.GetSheetAt(0);
            //获取有效行数
            int rows = sheet1.LastRowNum;
            //Console.WriteLine($"有效行数：{rows}");
            //结果集
            List<string[]> list = new List<string[]>(rows + 1);

            //遍历行
            for (int i = 0; i <= rows; i++)
            {
                //Console.WriteLine($"i：{i}");
                //获取行
                IRow row = sheet1.GetRow(i);
                //判断不为空
                if (row != null)
                {
                    //获取有效列数
                    int cells = row.LastCellNum;
                    //Console.WriteLine($"有效列数：{cells}");

                    //创建数组
                    string[] strArr = new string[cells];

                    //遍历列
                    for (int j = 0; j < cells; j++)
                    {
                        //获取单元格
                        ICell cell = row.GetCell(j);
                        string value = "";
                        //判断不为空
                        if (cell != null)
                        {
                            //获取单元格内容
                            value = cell.ToString();
                            //Console.WriteLine($"单元格内容：{value}");
                        }
                        //加入数组
                        strArr[j] = value;
                    }
                
                    //加入结果集
                    list.Add(strArr);
                }
            }

            //关闭Workbook
            workbook.Close();
            //返回结果集
            return list;
        }
    }
}
